import openpyxl
# 这个代码用来生成空的拼音

import main

workbook = openpyxl.load_workbook(r'D:\Windows\漳西全乡道路清单.xlsx')  # 依据哪一个Excel
excelFileName = "白塔镇加拼音"  # 新生成的Excel名称
excelStrLine = 'D'  # 中文列
excelPyLine = 'J'  # 拼音列
chinese_to_arabic = {'A': 1, 'B': 2, 'C': 3, 'D': 4, 'E': 5, 'F': 6, 'G': 7, 'H': 8, 'I': 9, 'J': 10, 'K': 11,
                     'L': 12, 'M': 13, 'N': 14}
# 获取工作表
sheet = workbook['Sheet1']

# 读取单元格数据
b_column_data = []

# 遍历文字列，判断是否为空
for index, cell in enumerate(sheet[f'{excelStrLine}']):
    # if cell.value == "#N/A" or cell.value is None:
        b_column_data.append((index + 1, cell.value))
    # 往数组里面存储拼音栏为空的 index和对应 中文信息

# 遍历数组内容
for index, value in b_column_data[1:]:
    if value is None:
        continue
    cell_value = sheet[f'{excelStrLine}{index}'].value
    pyStr = main.chinese_to_set_func(cell_value)
    sheet.cell(row=int(index), column=chinese_to_arabic[excelPyLine], value=pyStr)


# 关闭Excel文件
workbook.save(rf'D:\Windows\{excelFileName}.xlsx')
